安装mysql、hive步骤 一、什么是hive Hive是基于Hadoop的一个数据仓库工具(离线)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

操作接口采用类SQL语法，提供快速开发的能力， 避免了去写MapReduce，减少开发人员的学习成本， 功能扩展很方便。

用于解决海量结构化日志的数据统计。

本质是：将 HQL 转化成 MapReduce 程序

1、优缺点 优点：1) 操作接口采用类 SQL 语法，提供快速开发的能力（简单、容易上手）。

缺点： 1．Hive 的 HQL 表达能力有限

2、架构原理

二、启动方式 需要先启动hdfs和yarn，hive数据最终保存在hdfs，操作表需要mr，所以需要yarn资源调度

1、方式一：

[root@hdp-2 lib]# hive 设置一些基本参数，让hive使用起来更便捷，比如：

//让提示符显示当前库：

hive>set hive.cli.print.current.db=true;

//显示查询结果时显示字段名称：

hive>set hive.cli.print.header=true; 但是这样设置只对当前会话有效，重启hive会话后就失效，解决办法：

在linux的当前用户目录中~，编辑一个.hiverc文件，将参数写入其中：

vi .hiverc

set hive.cli.print.header=true; set hive.cli.print.current.db=true; 2、方式二：

在hdp-1服务器端启动hivesever2 （1）停在这个页面

[root@hdp-1 conf]# hiveserver2 启动hivesever2 （2）信息打印输出台

[root@hdp20-04 hive-1.2.1]# bin/hiveserver2 -hiveconf hive.root.logger=DEBUG,console 启动hivesever2 （2）后台启动

hiveserver2 1>/dev/null 2>&1 &

hive的安装在之前已经写了 地址：

一.语法 hive -help

在这里插入图片描述

“-e”不进入hive的交互窗口执行sql语句

“-f”执行脚本中sql语句 （1）在/opt/module/datas目录下创建hivef.sql文件

文件中写入正确的sql语句 select *from student; 执行文件中的sql语句

执行文件中的sql语句并将结果写入文件中 #bin/hive -f /opt/module/datas/hivef.sql > /opt/module/datas/hive_result.txt

退出hive

hive(default)>exit; hive(default)>quit;

在新版的hive中没区别了，在以前的版本是有的： exit:先隐性提交数据，再退出； quit:不提交数据，退出；

在hive cli命令窗口中如何查看hdfs文件系统

hive(default)>dfs -ls /;

在hive cli命令窗口中如何查看本地文件系统

hive(default)>! ls /opt/module/datas;

数据库操作 创建数据库 语法： CREATE DATABASE|SCHEMA [IF NOT EXISTS]

如：

exit

显示数据库 hive> show databases; 1 过滤显示查询的数据库 hive> show databases like ‘db_hive*’; OK db_hive db_hive_1

查看数据库详情 显示数据库信息 hive> desc database lk; OK db_name comment location owner_name owner_type parameters lk location/in/test root USER Time taken: 0.032 seconds, Fetched: 1 row(s)

2．显示数据库详细信息，extended

hive> desc database extended lk; OK db_name comment location owner_name owner_type parameters lk location/in/test root USER Time taken: 0.032 seconds, Fetched: 1 row(s)

切换当前数据库 hive> use lk;

修改数据库 用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

hive> alter database lk set dbproperties(‘createtime’=‘2019’);

在hive中查看修改结果

hive> desc database extended lk; OK db_name comment location owner_name owner_type parameters lk location/in/test root USER {createtime=2019} Time taken: 0.537 seconds, Fetched: 1 row(s)

删除数据库 1．删除空数据库 hive>drop database db_hive2;

2．如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

hive> drop database db_hive; FAILED: SemanticException [Error 10072]: Database does not exist: db_hive hive> drop database if exists db_hive2;

3．如果数据库不为空，可以采用cascade命令，强制删除

hive> drop database db_hive; FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.) hive> drop database db_hive cascade;

创建表 创建表的基本语法 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], …)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], …)] [CLUSTERED BY (col_name, col_name, …) [SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。 （2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。 （3）COMMENT：为表和列添加注释。 （4）PARTITIONED BY创建分区表 （5）CLUSTERED BY创建分桶表 （6）SORTED BY不常用 （7）ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)] 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。 SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。 （8）STORED AS指定存储文件类型 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。 （9）LOCATION ：指定表在HDFS上的存储位置。 （10）LIKE允许用户复制现有的表结构，但是不复制数据。

示例： 200101 陈白露 女 1980-02-10 2001 12312341234 北京市海淀区黄庄 200102 刘云飞 男 1980-10-01 2001 13612341234 北京市西城区56号 200103 张小强 男 1979-08-02 2001 19999228822 武汉市洪山区88号 CREATE TABLE IF NOT EXISTS students ( id int, name string, gender string, birthday Date, clazz string, phone string, loc string) COMMENT ‘student details’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ LINES TERMINATED BY ‘\n’ STORED AS TEXTFILE;

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。 案例实操 1） 假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为

{ “name”: “songsong”, “friends”: [“bingbing” , “lili”] , //列表Array, “children”: { //键值Map, “xiao song”: 18 , “xiaoxiao song”: 19 } “address”: { //结构Struct, “street”: “hui long guan” , “city”: “beijing” } }

2）基于上述数据结构，我们在Hive里创建对应的表，并导入数据。 创建本地测试文件test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用“_”。 3）Hive上创建测试表test

create table test( name string, friends array, children map, address struct ) row format delimited fields terminated by ‘,’ collection items terminated by ‘_’ map keys terminated by ‘:’ lines terminated by ‘\n’;

字段解释： row format delimited fields terminated by ‘,’ – 列分隔符 collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号) map keys terminated by ‘:’ – MAP中的key与value的分隔符 lines terminated by ‘\n’; – 行分隔符 4）导入文本数据到测试表

hive (default)> load data local inpath ‘/opt/module/datas/test.txt’into table test;

5）访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式

hive (default)> select friends[1],children[‘xiao song’],address.city from test where name=“songsong”; OK _c0 _c1 city lili 18 beijing Time taken: 0.076 seconds, Fetched: 1 row(s)

在这里插入图片描述

数据类型

可以使用CAST操作显示进行数据类型转换 例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。 在这里插入图片描述

修改表 ALTER TABLE name RENAME TO new_name ALTER TABLE name ADD COLUMNS (col_spec[, col_spec …]) ALTER TABLE name DROP [COLUMN] column_name ALTER TABLE name CHANGE column_name new_name new_type ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec …])

示例： hive> ALTER TABLE students RENAME TO stu;

删除表 DROP TABLE [IF EXISTS] table_name;

示例： hive> DROP TABLE IF EXISTS students;

如果要永久性删除，不准备再恢复（删除备份日志内的所有数据）： drop table students purge;

清空表数据 TRUNCATE TABLE [IF EXISTS] table_name;

示例： truncate table students;

插入数据 使用insert语句： insert into students values(‘200101’,‘陈白露’,‘女’,‘1980-02-10’,‘2001’,‘12312341234’,‘北京市海淀区黄庄’); 从本地导入： load data local inpath ‘/zzti/import/students.data’ into table students; 从HDFS导入： load data inpath ‘/data/zzti/import/students.data’ into table students

管理表与外部表 默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。 管理表和外部表的使用场景 每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。 创建部门表

create external table if not exists default.dept( deptno int, dname string, loc int ) row format delimited fields terminated by ‘\t’;

创建员工表

create external table if not exists default.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int) row format delimited fields terminated by ‘\t’;

（3）查看创建的表

hive > show tables; OK tab_name dept emp

（4）向外部表中导入数据 导入数据

hive > load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept; hive > load data local inpath ‘/opt/module/datas/emp.txt’ into table default.emp;

查询结果

hive > select * from emp; hive > select * from dept;

（5）查看表格式化数据

hive> desc formatted dept; Table Type: EXTERNAL_TABLE

管理表与外部表的互相转换 （1）查询表的类型

hive > desc formatted student2; Table Type: MANAGED_TABLE

（2）修改内部表student2为外部表

hive > alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);

（3）查询表的类型

hive > desc formatted student2; Table Type: EXTERNAL_TABLE

（4）修改外部表student2为内部表

hive > alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

（5）查询表的类型

hive > desc formatted student2; Table Type: MANAGED_TABLE

注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！

分区表 分区表实际就是对应hdfs文件系统上的的独立的文件夹，该文件是夹下是该分区所有数据文件。

CREATE external TABLE IF NOT EXISTS stup ( id int, name string, gender string, birthday date, phone string, loc string) partitioned by (clazz string) #按照什么分区（clazz 为分区类别，string为类型） ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’; #分隔符

为分区加载数据 hive> load data [local] inpath ‘/opt/module/datas/student.txt’ overwrite | into table student [partition (partcol1=val1,…)];

（1）load data:表示加载数据 （2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表 （3）inpath:表示加载数据的路径 （4）overwrite:表示覆盖表中已有数据，否则表示追加 （5）into table:表示加载到哪张表 （6）student:表示具体的表 （7）partition:表示上传到指定分区 3. 实操案例 （0）创建一张表

hive > create table student(id string, name string) row format delimited fields terminated by ‘\t’;

（1）加载本地文件到hive

hive > load data local inpath ‘/opt/module/datas/student.txt’ into table default.student;

（2）加载HDFS文件到hive中 上传文件到HDFS

hive > dfs -put /opt/module/datas/student.txt /user/atguigu/hive;

加载HDFS上数据

hive > load data inpath ‘/user/atguigu/hive/student.txt’ into table default.student;

（3）加载数据覆盖表中已有的数据 上传文件到HDFS

hive > dfs -put /opt/module/datas/student.txt /user/atguigu/hive;

加载数据覆盖表中已有的数据

hive > load data inpath ‘/user/atguigu/hive/student.txt’ overwrite into table default.student;

分区查询 单查询 select * from dept_partition where month=‘200109’;

联合查询

select * from dept_partition where month=‘201709’ union select * from dept_partition where month=‘201708’

增加分区 创建单个分区 hive > alter table dept_partition add partition(month=‘201706’) ;

同时创建多个分区

hive > alter table dept_partition add partition(month=‘201705’) partition(month=‘201704’);

删除分区 删除单个分区 hive > alter table dept_partition drop partition (month=‘201704’);

同时删除多个分区

hive > alter table dept_partition drop partition (month=‘201705’), partition (month=‘201706’);

查看分区表有多少分区 hive> show partitions dept_partition;

查看分区表结构 hive> desc formatted dept_partition;

通过查询语句向表中插入数据 1．创建一张分区表

hive > create table student(id int, name string) partitioned by (month string) row format delimited fields terminated by ‘\t’;

2．基本插入数据

hive > insert into table student partition(month=‘201709’) values(1,‘wangwu’);

3．基本模式插入（根据单张表查询结果）

hive > insert overwrite table student partition(month=‘201708’) select id, name from student where month=‘201709’;

select语句 紧跟列名，也可以在列名和别名之间加入关键字‘AS’ 4．案例实操 查询名称和部门

hive > select ename AS name, deptno dn from emp;

SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING having_condition] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] [LIMIT number];

1．求总行数（count） hive (default)> select count(*) cnt from emp; 2．求工资的最大值（max） hive (default)> select max(sal) max_sal from emp; 3．求工资的最小值（min） hive (default)> select min(sal) min_sal from emp; 4．求工资的总和（sum） hive (default)> select sum(sal) sum_sal from emp; 5．求工资的平均值（avg） hive (default)> select avg(sal) avg_sal from emp; 6.1.5 Limit语句 典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。 hive (default)> select * from emp limit 5;

where

（1）查询出薪水等于5000的所有员工 hive (default)> select * from emp where sal =5000; （2）查询工资在500到1000的员工信息 hive (default)> select * from emp where sal between 500 and 1000; （3）查询comm为空的所有员工信息 hive (default)> select * from emp where comm is null; （4）查询工资是1500或5000的员工信息 hive (default)> select * from emp where sal IN (1500, 5000);

示例： hive> SELECT * FROM students WHERE phone=‘13311331133’;

Like和RLike 1）使用LIKE运算选择类似的值 2）选择条件可以包含字符或数字: % 代表零个或多个字符(任意个字符)。 _ 代表一个字符。 3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。 4）案例实操 （1）查找以2开头薪水的员工信息

hive (default)> select * from emp where sal LIKE ‘2%’; 1 (2）查找第二个数值为2的薪水的员工信息

hive (default)> select * from emp where sal LIKE ‘_2%’; 1 (3）查找薪水中含有2的员工信息

hive (default)> select * from emp where sal RLIKE ‘[2]’; 1 HIVE内置函数——数据计算 在这里插入图片描述

ORDER BY SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING having_condition] [ORDER BY col_list]] [LIMIT number]; 按照字典序以某列为排序对象对数据排序 对输入做全局排序，只有一个reducer（且不能通过改配置优化这一点） 因此当输入规模较大时，需要较长的计算时间

示例：

hive> SELECT id, name, phone FROM students ORDER BY phone;

1 2 Having语句 1．having与where不同点 （1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。 （2）where后面不能写分组函数，而having后面可以使用分组函数。 （3）having只用于group by分组统计语句。 2．案例实操 （1）求每个部门的平均薪水大于2000的部门 求每个部门的平均工资

hive > select deptno, avg(sal) from emp group by deptno; 1 求每个部门的平均薪水大于2000的部门

hive > select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000; 1 2 GROUP BY SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING having_condition] [ORDER BY col_list]] [LIMIT number]; 按照某些字段的值进行分组，有相同值放到一起。 使用了reduce操作，受限于reduce数量，设置reduce参数mapred.reduce.tasks 输出文件个数与reduce数相同，文件大小与reduce处理的数据量有关。 示例： hive> SELECT phone,count(*) FROM students GROUP BY phone;

DISTINCT hive> select distinct name from test; …

OK zhao Time taken: 37.047 seconds, Fetched: 1 row(s)

hive> select distinct name,age from test; OK zhao 14 zhao 15 zhao 16 Time taken: 39.131 seconds, Fetched: 3 row(s)

hive> select distinct(name),age from test; OK zhao 14 zhao 15 zhao 16 Time taken: 37.739 seconds, Fetched: 3 row(s)

JOIN join_table: table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition | table_reference CROSS JOIN table_reference [join_condition]

两个表m,n之间按照on条件连接，m中的一条记录和n中的一条记录组成一条新记录。 join等值连接（内连接），只有某个值在m和n中同时存在时。 left outer join左外连接，左边表中的值无论是否在b中存在时，都输出；右边表中的值，只有在左边表中存在时才输出。 right outer join和left outer join相反。 left semi join类似exists。即查找a表中的数据，是否在b表中存在，找出存在的数据。

hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT

FROM CUSTOMERS c JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);

cluster by cluster by的功能就是distribute by和sort by相结合，如下2个语句是等价的

select mid, money, name from store cluster by mid select mid, money, name from store distribute by mid sort by mid

HIVE内置函数——字符串相关 在这里插入图片描述

HIVE内置函数——时间相关 在这里插入图片描述

HIVE内置函数——高级函数 在这里插入图片描述